package com.miaomi.Dao.impl;

import com.miaomi.Bean.Tel_sys;
import com.miaomi.Dao.TelephoneDao;
import com.miaomi.Util.jdbcUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.List;

public class TelephoneDaoImpl implements TelephoneDao {
    private static Tel_sys t = new Tel_sys();

    @Override
    public int insert(Tel_sys t) {
        String sql = "INSERT INTO `tel_sys` (`name`,`age`,`gender`,`tel`,`qq`,`addr`) VALUES (?,?,?,?,?,?)";
        int result = 0;
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,t.getName());
            preparedStatement.setObject(2,t.getAge());
            preparedStatement.setObject(3,t.getGender());
            preparedStatement.setObject(4,t.getTel());
            preparedStatement.setObject(5,t.getQq());
            preparedStatement.setObject(6,t.getAddr());
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
            return result;
    }

    @Override
    public int delete(Integer id) {
        String sql = "DELETE FROM `tel_sys` WHERE `id` = ? ";
        int result = 0;
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,id);
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int update(Tel_sys t) {
        String sql = "UPDATE `tel_sys` SET `name` = ?,`age` = ?,`gender` = ?,`tel` = ?,`qq` = ?,`addr` = ? WHERE `id` = ? ";
        int result = 0;
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,t.getName());
            preparedStatement.setObject(2,t.getAge());
            preparedStatement.setObject(3,t.getGender());
            preparedStatement.setObject(4,t.getTel());
            preparedStatement.setObject(5,t.getQq());
            preparedStatement.setObject(6,t.getAddr());
            preparedStatement.setObject(7,t.getId());
            result = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public List<Tel_sys> selectAll() {
        String sql = "SELECT * FROM `tel_sys`";
        List<Tel_sys> list = new ArrayList<>();
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Tel_sys telSys = new Tel_sys();
                telSys.setId(resultSet.getInt("id"));
                telSys.setName(resultSet.getString("name"));
                telSys.setAge(resultSet.getInt("age"));
                telSys.setGender(resultSet.getString("gender"));
                telSys.setTel(resultSet.getString("tel"));
                telSys.setQq(resultSet.getString("qq"));
                telSys.setAddr(resultSet.getString("addr"));

                list.add(telSys);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Tel_sys> selectName(String name) {
        String sql = "SELECT * FROM `tel_sys` WHERE `name` = ?";
        List<Tel_sys> list = new ArrayList<>();
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,name);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Tel_sys telSys = new Tel_sys();
                telSys.setId(resultSet.getInt("id"));
                telSys.setName(resultSet.getString("name"));
                telSys.setAge(resultSet.getInt("age"));
                telSys.setGender(resultSet.getString("gender"));
                telSys.setTel(resultSet.getString("tel"));
                telSys.setQq(resultSet.getString("qq"));
                telSys.setAddr(resultSet.getString("addr"));

                list.add(telSys);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Tel_sys> selectId(Integer id) {
        String sql = "SELECT * FROM `tel_sys` WHERE `id` = ?";
        List<Tel_sys> list = new ArrayList<>();
        try {
            Connection connection = jdbcUtil.getconnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Tel_sys telSys = new Tel_sys();
                telSys.setId(resultSet.getInt("id"));
                telSys.setName(resultSet.getString("name"));
                telSys.setAge(resultSet.getInt("age"));
                telSys.setGender(resultSet.getString("gender"));
                telSys.setTel(resultSet.getString("tel"));
                telSys.setQq(resultSet.getString("qq"));
                telSys.setAddr(resultSet.getString("addr"));

                list.add(telSys);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
